SET AUTOCOMMIT OFF;
SET PAGESIZE 100;
SET LINESIZE 100;

SET AUTOCOMMIT OFF;
SET PAGESIZE 100;
SET LINESIZE 100;

DROP TABLE AlbumContient;
DROP TABLE Article;
DROP TABLE Album;
DROP TABLE Livre ;
DROP TABLE Image;
DROP TABLE Commande;
DROP TABLE Client;
DROP TABLE Format;
DROP TABLE Appareil;
DROP SEQUENCE commande_sequence;
DROP SEQUENCE article_sequence;
DROP SEQUENCE image_sequence;
DROP SEQUENCE album_sequence;
DROP SEQUENCE livre_sequence;

CREATE TABLE Appareil (
	idApp				NUMBER ,
	nom 				VARCHAR2(25) NOT NULL,
	objectif			VARCHAR2(25) NOT NULL,
	distFocale			NUMBER NOT NULL,
	sensibiliteIso			NUMBER NOT NULL,
	ouverture			NUMBER(2,2) NOT NULL,
	vitesseObturation		NUMBER NOT NULL,
	nbPixel				NUMBER NOT NULL,
	CONSTRAINT pk_Appareil PRIMARY KEY(idApp)
) ;

CREATE TABLE Format (
	idFormat		NUMBER ,
	libelle			VARCHAR2(25) NOT NULL,
	prixFormat		NUMBER(4,2) NOT NULL,
	CONSTRAINT pk_Format PRIMARY KEY(idFormat)
) ;

CREATE TABLE Client (
	mail			VARCHAR2(100) ,
	motDePasse		VARCHAR2(8) NOT NULL,
	nom			VARCHAR2(25),
	prenom			VARCHAR2(25),
	adresse			VARCHAR2(40)NOT NULL,
	codePostal		NUMBER NOT NULL,
	ville			VARCHAR(20) NOT NULL,
	CONSTRAINT pk_Client PRIMARY KEY(mail)
) ;

CREATE TABLE Commande (
	idCommande			NUMBER,
	dateCommande			TIMESTAMP(6) NOT NULL,
	prixTotal			NUMBER(5,2) NOT NULL,
	adresseExp			VARCHAR2(25) NOT NULL,
	codePostalExp			NUMBER NOT NULL,
	villeExp			VARCHAR2(20) NOT NULL,
	idClient			VARCHAR2(100),
	CONSTRAINT pk_Commande PRIMARY KEY(idCommande),
	CONSTRAINT fk_Commande_idClient FOREIGN KEY(idClient) REFERENCES Client(mail)
) ;

CREATE TABLE Image (
	idImage			NUMBER,
	chemin			VARCHAR2(500) NOT NULL,
	idApp			NUMBER,
	CONSTRAINT pk_Image PRIMARY KEY(idImage),
	CONSTRAINT fk_Image_idApp FOREIGN KEY(idApp) REFERENCES Appareil(idApp)
) ;

CREATE TABLE Livre (
	idLivre			NUMBER,
	preface			VARCHAR2(100),
	postface		VARCHAR2(100),
	CONSTRAINT pk_Livre PRIMARY KEY(idLivre),
) ;

CREATE TABLE Album (
	idAlbum			NUMBER,
	titre			VARCHAR2(20) NOT NULL,
	soustitre		VARCHAR2(20),
	visible			NUMBER CHECK ( visible >= 0 AND visible <= 1 ) NOT NULL,
	idLivre			NUMBER,
	idClient		VARCHAR2(100),
	CONSTRAINT pk_Album PRIMARY KEY(idAlbum),
	CONSTRAINT fk_Album_idLivre  FOREIGN KEY(idLivre)  REFERENCES Livre(idLivre),
	CONSTRAINT fk_Album_idClient FOREIGN KEY(idClient) REFERENCES Client(mail)
) ;


CREATE TABLE Article (
	idArticle 		NUMBER ,
	qte			NUMBER NOT NULL,
	idCommande		NUMBER NOT NULL,
	idFormat		NUMBER,
	idAlbum			NUMBER,
	CONSTRAINT pk_Article PRIMARY KEY(idArticle),
	CONSTRAINT fk_Article_idCommande FOREIGN KEY(idCommande) REFERENCES Commande(idCommande),
	CONSTRAINT fk_Article_idFormat   FOREIGN KEY(idFormat)   REFERENCES Format(idFormat),
	CONSTRAINT fk_Article_idAlbum    FOREIGN KEY(idAlbum)    REFERENCES Album(idAlbum)
) ;



CREATE TABLE AlbumContient (
	idAlbum			NUMBER,
	idImage			NUMBER,
	numOrdre		NUMBER NOT NULL,
	titre			VARCHAR2(10),
	commentaire		VARCHAR2(20),
	CONSTRAINT pk_AlbumContient PRIMARY KEY(idAlbum,idImage),
	CONSTRAINT fk_AlbumContient_idAlbum   FOREIGN KEY(idAlbum)   REFERENCES Album(idAlbum),
	CONSTRAINT fk_AlbumContient_idImage   FOREIGN KEY(idImage)   REFERENCES Image(idImage)
) ;

CREATE SEQUENCE commande_sequence
START WITH 1
INCREMENT BY 1;

CREATE OR REPLACE TRIGGER commande_trigger
BEFORE INSERT
ON commande
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT commande_sequence.nextval INTO :NEW.idCommande FROM dual;
END;
/

CREATE SEQUENCE article_sequence
START WITH 1
INCREMENT BY 1;

CREATE OR REPLACE TRIGGER article_trigger
BEFORE INSERT
ON article
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT article_sequence.nextval INTO :NEW.idarticle FROM dual;
END;
/

CREATE SEQUENCE image_sequence
START WITH 1
INCREMENT BY 1;

CREATE OR REPLACE TRIGGER image_trigger
BEFORE INSERT
ON image
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT image_sequence.nextval INTO :NEW.idimage FROM dual;
END;
/

CREATE SEQUENCE album_sequence
START WITH 1
INCREMENT BY 1;

CREATE OR REPLACE TRIGGER album_trigger
BEFORE INSERT
ON album
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT album_sequence.nextval INTO :NEW.idalbum FROM dual;
END;
/

CREATE SEQUENCE livre_sequence
START WITH 1
INCREMENT BY 1;

CREATE OR REPLACE TRIGGER livre_trigger
BEFORE INSERT
ON livre
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT livre_sequence.nextval INTO :NEW.idlivre FROM dual;
END;
/


/**
 * Insertaion des formats
 *
 */
insert into format
 values(1,'10x15',3.00);
insert into format
 values(2,'13x18',3.20);
insert into format
 values(3,'13x19',3.40);
insert into format
 values(4,'15x21',4.2);
insert into format
 values(5,'15x23',5.00);
insert into format
 values(6,'20x30',7.20);
insert into format
 values(7,'9x13',7.00);
insert into format
 values(8,'11x15',9.00);
insert into format
 values(9,'11x20',16.00);
insert into format
 values(10,'13x17',6.90);
insert into format
 values(11,'15x20',4.20);
insert into format
 values(12,'20x27',3.89);
insert into format
 values(13,'15x20',7.50);
insert into format
 values(14,'20x27',8.00);
insert into format
 values(15,'20x30',9.00);


commit;